package Dao.TitleAndPaper.Imp;

import Dao.TitleAndPaper.TitleDao;
import Entity.TitleAndPaper.Title;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TitleDaoImp implements TitleDao {


    private PreparedStatement ps = null;
    private ResultSet rs = null;



    @Override
    public List<Title> queryAllTitle(String queryKey, Integer offset, Integer limit, Integer type, Connection conn) {
        List<Title> titles = new ArrayList<>();
        try {
            String sql = "select xt_id, title, intr, `from`, categoty, nature, addtime, no, state, college_name, major_name, opinoin, applly, is_pass,tname,tno,sname " +
                    "from title where title like ? and applly = ? limit ?,?";
            //type  1 申请人为学生  2 申请人为老师

            ps = conn.prepareStatement(sql);
            ps.setString(1, queryKey);
            if (type == 1) {
                ps.setString(2, "学生");
            } else if (type == 2) {
                ps.setString(2, "老师");
            } else {
                return titles;
            }
            ps.setInt(3, offset);
            ps.setInt(4, limit);
            rs = ps.executeQuery();
            while (rs.next()) {
                Title title1 = this.setTitleByRs();
                titles.add(title1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return titles;
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return titles;
    }

    @Override
    public Integer insertTitle(Title title,Integer type, Connection conn) {
        int result = 0;
        try {
            String sql = "insert into title( title, intr, `from`, categoty, nature, addtime, no, state, college_name, major_name, opinoin, applly, is_pass,tname,tno,sname) " +
                    "values (?,?,?,?,?,now(),?,'审核中', ?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,title.getTitle());
            ps.setString(2,title.getIntr());
            ps.setString(3,title.getFrom());
            ps.setString(4,title.getCategoty());
            ps.setString(5,title.getNature());
            ps.setString(6,title.getNo());
            ps.setString(7,title.getCollegeName());
            ps.setString(8,title.getMajorName());
            ps.setString(9,title.getOpinoin());
            if (type == 1){
                ps.setString(10,"学生");
            }else if (type == 2){
                ps.setString(10,"老师");
            }
            ps.setString(11,title.getIsPass());
            ps.setString(12,title.getTName());
            ps.setString(13,title.getTno());
            ps.setString(14,title.getSname());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtil.closeMySQLResource( ps, rs);
        }
        return result;
    }

    @Override
    public Integer updateIsPass(String result, String opinion, Integer id, Connection conn) {
        int rsSum = 0;
        try {
            String sql = "update title set opinoin = ?, is_pass = ? ,state = '审核完成' where xt_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,opinion);
            ps.setString(2,result);
            ps.setInt(3,id);
            rsSum = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtil.closeMySQLResource( ps, rs);
        }
        return  rsSum;
    }

    @Override
    public Title getTitleById(Integer id ,Connection conn) {
        Title title = null;
        String sql = "select xt_id, title, intr, `from`, categoty, nature, addtime, no, state, college_name, major_name, opinoin, applly, is_pass,tname,tno,sname " +
                "from title where xt_id = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if (rs.next()){
                title = setTitleByRs();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeMySQLResource(ps,rs);
        }
        return title;
    }






















    public Title setTitleByRs() throws SQLException {
        Integer xtId = rs.getInt("xt_id");
        String title = rs.getString("title");
        String intr = rs.getString("intr");
        String from = rs.getString("from");
        String categoty = rs.getString("categoty");
        String nature = rs.getString("nature");
        String addtime = rs.getString("addtime");
        String no = rs.getString("no");
        String state = rs.getString("state");
        String collegeName = rs.getString("college_name");
        String majorName = rs.getString("major_name");
        String opinoin = rs.getString("opinoin");
        String applly = rs.getString("applly");
        String isPass = rs.getString("is_pass");
        String tNo = rs.getString("tno");
        String tName = rs.getString("tname");
        String sname = rs.getString("sname");
        return new Title(xtId, title, intr, from, categoty, nature, addtime, no, state, collegeName, majorName, opinoin, applly, isPass, tName, tNo, sname);
    }
}
